package org.example.library.dao;

import org.example.library.util.JDBCutils;
import org.example.library.vertile.Book;

import java.sql.*;

public class BookDao {

    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection connection = null;

    public int insertBook(Book book){
        int count = 0;
        try{
            connection = JDBCutils .getconnection();
            String sql = "INSERT INTO tb_book (number,name,author) VALUE (?,?,?)";
            ps = connection.prepareStatement(sql);
            String number = book.getNumber();
            String name = book.getName();
            String author = book.getAuthor();

            ps.setString(1,number);
            ps.setString(2,name);
            ps.setString(3,author);
            count = ps.executeUpdate();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCutils.release(rs,ps,connection);//调用静态方法
//            jdbcutils.release(rs,ps,connection);//实例调用
            return count;
        }
    }
    public int deleteBook(String number){
        int count = 0;
        try{
            connection = JDBCutils.getconnection();
            String sql = "DELETE FROM tb_book WHERE number=?";
            ps = connection.prepareStatement(sql);
            ps.setString(1,number);
            count = ps.executeUpdate();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCutils.release(rs,ps,connection);
//            jdbcutils.release(rs,ps,connection);
            return count;
        }
    }
    public Book updateBook(Book book){
        Book book1 = new Book();
        try{
            connection = JDBCutils.getconnection();
            String sql = "UPDATE FROM tb_book SET name=?,author=? WHERE number=?";
            ps = connection.prepareStatement(sql);
            ps.setString(1, book.getName());
            ps.setString(2, book.getAuthor());
            ps.setString(3, book.getNumber());
            rs = ps.executeQuery();
            if(rs.next()){
                String name=rs.getString("name");
                String author=rs.getString("author");
                book1.setAuthor(author);
                book1.setName(name);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            JDBCutils.release(rs,ps,connection);
            return book1;
        }
    }
    public Book searchBook(String number){
        Book book = new Book();
        try {
            connection = JDBCutils.getconnection();
            String sql = "SELECT * FROM tb_book WHERE number=?";
            ps = connection.prepareStatement(sql);
            ps.setString(1,number);
            rs = ps.executeQuery();
            if(rs.next()){

                String name = rs.getString("name");
                String author = rs.getString("author");
                book.setName(name);
                book.setNumber(number);
                book.setAuthor(author);
            }
        }catch (Exception e){
            e.getStackTrace();
        }finally {
            JDBCutils.release(rs,ps,connection);
            return book;
        }
    }
}
